#!/usr/bin/env python
# -*- coding: utf-8 -*-

from django.db import connection
from web.dao.base_dao import BaseDao
from web.models.commodity_future_exchange import CommodityFutureExchange

"""
CommodityFutureExchange的dao类
"""


class CommodityFutureExchangeDao(BaseDao):
    model_class = CommodityFutureExchange

    def add_oracle_column_comment(self):
        """
        为表commodity_future_exchange添加注释
        """

        with connection.cursor() as cursor:
            cursor.execute("comment on table commodity_future_exchange is \'商品期货交易所\'")
            cursor.execute("COMMENT ON COLUMN commodity_future_exchange.id IS \'主键\'")
            cursor.execute("COMMENT ON COLUMN commodity_future_exchange.name IS \'名称\'")
            cursor.execute("COMMENT ON COLUMN commodity_future_exchange.code IS \'代码\'")
            cursor.execute("COMMENT ON COLUMN commodity_future_exchange.akshare_contract_suffix IS \'akshare合约后缀\'")
            cursor.execute("COMMENT ON COLUMN commodity_future_exchange.tianqin_contract_prefix IS \'tianqin合约前缀\'")
            cursor.execute("COMMENT ON COLUMN commodity_future_exchange.home_url IS \'首页地址\'")

    def find_all(self):
        """
        查询commodity_future_exchange表的所有记录
        """

        return self.find_list(dict(), dict(), list())

    def concat_tianqin_contract_prefix_and_code(self):
        """
        拼接交易所前缀和期货代码
        """

        with connection.cursor() as cursor:
            cursor.execute("select cfe.tianqin_contract_prefix || UPPER(cfi.CODE) "
                           "from COMMODITY_FUTURE_INFO cfi "
                           "join COMMODITY_FUTURE_EXCHANGE cfe on cfe.ID=cfi.COMMODITY_FUTURE_EXCHANGE_ID and cfe.CODE in('CZCE','CFFEX') "
                           "UNION "
                           "select cfe.tianqin_contract_prefix || LOWER(cfi.CODE) "
                           "from COMMODITY_FUTURE_INFO cfi "
                           "join COMMODITY_FUTURE_EXCHANGE cfe on cfe.ID=cfi.COMMODITY_FUTURE_EXCHANGE_ID and cfe.CODE in('SHFE','DCE','INE','GFEX')")
            tianqin_contract_prefix_and_code_list = cursor.fetchall()
            return tianqin_contract_prefix_and_code_list
        